/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package aptech.semester.code.data;

import aptech.semester.code.conn.MyLib;
import aptech.semester.code.entity.SalaryFormEntity;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


/**
 *
 * @author vi
 */
public class SalaryFormList {
    
   
    
    public SalaryFormEntity getSalaryFormByDateAndDept(int year, int month, int dept) {
        SalaryFormEntity salaryForm = new SalaryFormEntity();
      
        try {
            //1.tao 1 ket noi
            Connection cn = MyLib.getCN();
            if(cn!=null) {
            String sql = "SELECT * FROM SALARY_FORM " +
                        "WHERE MONTH = ? " +
                        "AND YEAR = ? "+
                        "AND DEPARTMENT = ?";
            PreparedStatement pst = cn.prepareStatement(sql);
            pst.setInt(1,month);
            pst.setInt(2,year);
            pst.setInt(3,dept);
            //3.cho thi hanh lenh truy van tren doi tuong nay va ket qua se dc tra ve doi tuong resulltset
            ResultSet rs = pst.executeQuery();
            
            //4. duyuet ket qua tra ve trong resultset rs va fill vao arraylist ds.
            while(rs.next()) {
                salaryForm.setSalaryFormId(rs.getInt("ID"));
                salaryForm.setYearCalculate(rs.getInt("YEAR"));
                salaryForm.setMonthCalculate(rs.getInt("MONTH"));
                salaryForm.setTotalWorkingDay(rs.getInt("TOTAL_WORKDAY"));
            }
            rs.close();
            cn.close();
            }
        } catch (Exception e) {
            
        }
        
        return salaryForm;
    }
    
    public SalaryFormEntity getSalaryFormByDate(int year, int month) {
        SalaryFormEntity salaryForm = new SalaryFormEntity();
      
        try {
            //1.tao 1 ket noi
            Connection cn = MyLib.getCN();
            if(cn!=null) {
            String sql = "SELECT * FROM SALARY_FORM " +
                        "WHERE MONTH = ? " +
                        "AND YEAR = ?";
            PreparedStatement pst = cn.prepareStatement(sql);
            pst.setInt(1,month);
            pst.setInt(2,year);
            //3.cho thi hanh lenh truy van tren doi tuong nay va ket qua se dc tra ve doi tuong resulltset
            ResultSet rs = pst.executeQuery();
            
            //4. duyuet ket qua tra ve trong resultset rs va fill vao arraylist ds.
            while(rs.next()) {
                salaryForm.setSalaryFormId(rs.getInt("ID"));
                salaryForm.setYearCalculate(rs.getInt("YEAR"));
                salaryForm.setMonthCalculate(rs.getInt("MONTH"));
                salaryForm.setTotalWorkingDay(rs.getInt("TOTAL_WORKDAY"));
            }
            rs.close();
            cn.close();
            }
        } catch (Exception e) {
            
        }
        
        return salaryForm;
    }
    
//    public List<SalaryFormEntity> getSalaryFormByPeriod(int fromMonth, int toMonth, int year) {
//        List<SalaryFormEntity> listSalaryForm = new ArrayList<SalaryFormEntity>();
//      
//        try {
//            //1.tao 1 ket noi
//            Connection cn = MyLib.getCN();
//            if(cn!=null) {
//            String sql = "SELECT * FROM SALARY_FORM " +
//                        "WHERE MONTH BETWEEN ? AND ? " +
//                        "AND YEAR = ?";
//            PreparedStatement pst = cn.prepareStatement(sql);
//            pst.setInt(1,fromMonth);
//            pst.setInt(1,toMonth);
//            pst.setInt(2,year);
//            //3.cho thi hanh lenh truy van tren doi tuong nay va ket qua se dc tra ve doi tuong resulltset
//            ResultSet rs = pst.executeQuery();
//            
//            //4. duyuet ket qua tra ve trong resultset rs va fill vao arraylist ds.
//            while(rs.next()) {
//                SalaryFormEntity salaryForm = new SalaryFormEntity();
//                salaryForm.setSalaryFormId(rs.getInt("ID"));
//                salaryForm.setYearCalculate(rs.getInt("YEAR"));
//                salaryForm.setMonthCalculate(rs.getInt("MONTH"));
//                salaryForm.setTotalWorkingDay(rs.getInt("TOTAL_WORKDAY"));
//                
//                listSalaryForm.add(salaryForm);
//            }
//            rs.close();
//            cn.close();
//            }
//        } catch (Exception e) {
//            
//        }
//        
//        return listSalaryForm;
//    }
    
    public List<SalaryFormEntity> getSalaryFormByYear(int year) {
        List<SalaryFormEntity> listSalaryForm = new ArrayList<SalaryFormEntity>();
      
        try {
            //1.tao 1 ket noi
            Connection cn = MyLib.getCN();
            if(cn!=null) {
            String sql = "SELECT * FROM SALARY_FORM WHERE YEAR = ? ";
                        
            PreparedStatement pst = cn.prepareStatement(sql);
            pst.setInt(1,year);

            //3.cho thi hanh lenh truy van tren doi tuong nay va ket qua se dc tra ve doi tuong resulltset
            ResultSet rs = pst.executeQuery();
            
            //4. duyuet ket qua tra ve trong resultset rs va fill vao arraylist ds.
            while(rs.next()) {
                SalaryFormEntity form = new SalaryFormEntity();
                form.setSalaryFormId(rs.getInt("ID"));
                form.setYearCalculate(rs.getInt("YEAR"));
                form.setMonthCalculate(rs.getInt("MONTH"));
                form.setTotalWorkingDay(rs.getInt("TOTAL_WORKDAY"));
                
                listSalaryForm.add(form);
            }
            rs.close();
            cn.close();
            }
        } catch (Exception e) {
            
        }
        
        return listSalaryForm;
    }
    
    public int insert(SalaryFormEntity salaryForm) {
        int id = 0;
        try {
            //1. tao ket noi
            Connection cn = MyLib.getCN();
            
            //2.tao doi tuong lenh insert sql
            String sql = "insert SALARY_FORM values(?,?,?,?)";
            PreparedStatement pst = cn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            pst.setInt(1, salaryForm.getYearCalculate());
            pst.setInt(2, salaryForm.getTotalWorkingDay());
            pst.setInt(3, salaryForm.getDepartment());
            pst.setInt(4, salaryForm.getMonthCalculate());
            //3.cho thi hanh cau lenh sql
            pst.executeUpdate();
            
            ResultSet rs = pst.getGeneratedKeys();
            
            while (rs.next()) {
                id = rs.getInt(1);
            }
            
            pst.close();
            cn.close();
        } catch (Exception e) {
            e.printStackTrace();
            
        }
        return id;
    }
}
